from flask import Flask,render_template,request,redirect,url_for
from flask import g,send_from_directory,flash ,session,make_response
import sqlite3
from datetime import datetime,timedelta
from werkzeug.security import generate_password_hash,check_password_hash
import os
from account.views import RegUser,UserLogin,RegAdmin
from article.views import article

# app中设置静态子目录时 static_url_path=''
app = Flask(__name__)
app.debug=True
app.secret_key='whosssssofie*&%$3435'
app.register_blueprint(article,url_prefix='/news/')

DATABASE_URL= r'.\db\feedback.db'
UPLOAD_FOLDER = r'.\uploads'
ALLOWED_EXTENSIONS = ['.jpg','.png','.gif']


# 用来呈现特定目录下的资源 - 图像
@app.route('/profile/<filename>')
def rend_file(filename):
    return send_from_directory(UPLOAD_FOLDER,filename)

# 检查文件是否允许上传
def allowed_file(filename):
    _ , ext = os.path.splitext(filename)
    return ext in ALLOWED_EXTENSIONS

# 将游标获取的Tuple根据数据库列名转换为dict
def make_dicts(cursor,row):
    return dict((cursor.description[i][0],value) for i,value in enumerate(row))

# 获取 建立数据库连接
def get_db():
    db = getattr(g,'_database',None)
    if db is None:
        db=g._database = sqlite3.connect(DATABASE_URL)
        db.row_factory = make_dicts
    return db

# 执行SQL语句不返回数据结果
def execute_sql(sql,prms=()):
    c = get_db().cursor()
    c.execute(sql,prms)
    c.connection.commit()
    c.close()

#     执行用于选择数据的sql
def query_sql(sql,prms=(),one=False):
    c = get_db().cursor()
    result=c.execute(sql,prms).fetchall()
    c.close()
    return (result[0] if result else None ) if one else result

# 关闭连接
@app.teardown_appcontext
def close_connection(exeption):
    db = getattr(g,'_database',None)
    if db is not None:
        db.close()

@app.route('/')
def index():
    # return generate_password_hash('654321')
    return redirect(url_for('feedback'))

@app.route('/feedback/')
def feedback():
    # conn = sqlite3.connect(DATABASE_URL)
    # c = conn.cursor()
    sql = 'select rowid,* from category'
    # categorys = c.execute(sql).fetchall()
    categorys = query_sql(sql)
    # c.close()
    # conn.close()
    return render_template('post.html',categorys=categorys)

@app.route('/post_feedback/',methods=['POST'])
def post_feedback():
    if request.method == 'POST':
        subject = request.form['subject']
        categoryid = request.form.get('category',1) # get()可以指定默认值
        username = request.form.get('username')
        email = request.form.get('email')
        body = request.form.get('body')
        release_time=datetime.now()
        is_processed ='0' # State
        img_path = None
        img = request.files['up_image'] # img.filename 上传的文件名
        if 'up_image' in request.files and allowed_file(img.filename):
            img_path = datetime.now().strftime('%Y%m%d%H%M%f')+os.path.splitext(img.filename)[1]
            img.save(os.path.join(UPLOAD_FOLDER,img_path))
        # conn = sqlite3.connect(DATABASE_URL)
        # c = conn.cursor()
        sql = 'insert into feedback (Subject,CategoryID,UserName,Email,Body,RelerseTime,State,Image) values (?,?,?,?,?,?,?,?)'
        execute_sql(sql,(subject,categoryid,username,email,body,release_time,is_processed,img_path))
        # c.execute(sql,(subject,categoryid,username,email,body,release_time,is_processed))
        # conn.commit()
        # conn.close()
        return redirect(url_for('feedback'))

@app.route('/admin/list/')
def feedback_list():
    if session.get('admin',None) is None:
        return redirect(url_for('login'))
    else:
        key = request.args.get('key','')
        sql = 'select f.ROWID,* from feedback f inner join category c on f.CategoryID = c.ROWID where f.Subject like ?  order by f.ROWID DESC'
        feedbacks = query_sql(sql,('%{}%'.format(key),))
        # feedbacks = c.execute(sql).fetchall()
        # conn.close()
        return render_template('feedback-list.html',items=feedbacks)

@app.route('/admin/edit/<id>/')
def edit_feedback(id=0):
    if session.get('admin',None) is None:
        return redirect(url_for('login'))
    else:
        # conn = sqlite3.connect(DATABASE_URL)
        # c = conn.cursor()
        sql = 'select rowid,* from category'
        # categorys = c.execute(sql).fetchall()
        categorys = query_sql(sql)
        sql = 'select *,ROWID from feedback where ROWID=?'
        # items=c.execute(sql,(id,)).fetchone()
        items = query_sql(sql,id,True)
        # c.close()
        # conn.close()
        return render_template('edit.html',categorys=categorys,items=items)

@app.route('/admin/save_edit/',methods=['POST'])
def save_feedback():
    if request.method == 'POST':
        subject = request.form['subject']
        categoryid = request.form.get('category',1) # get()可以指定默认值
        username = request.form.get('username')
        email = request.form.get('email')
        body = request.form.get('body')
        reply = request.form.get('reply')
        release_time=request.form.get('releasetime')
        # checked 返回的值 on null
        is_processed = '1' if request.form.get('isprocessed','') else '0' # State
        rowid=request.form.get('rowid')
        # conn = sqlite3.connect(DATABASE_URL)
        # c = conn.cursor()
        sql = ''' update feedback set
            Subject=?,
            CategoryID=?,
            UserName=?,
            Email=?,
            Body=?,
            Reply=?,
            RelerseTime=?,
            State=? where ROWID=?
        '''
        execute_sql(sql,(subject,categoryid,username,email,body,reply,release_time,is_processed,rowid))
        # c.execute(sql,(subject,categoryid,username,email,body,reply,release_time,is_processed,rowid))
        # conn.commit()
        # conn.close()
        return redirect(url_for('feedback_list'))

@app.route('/admin/del/<id>/')
def delete_feedback(id=0):
    if session.get('admin',None) is None:
        return redirect(url_for('login'))
    else:
        # conn = sqlite3.connect(DATABASE_URL)
        # c = conn.cursor()
        sql='delete from feedback where ROWID=?'
        execute_sql(sql,(id,))
        # c.execute(sql,(id,))
        # conn.commit()
        # conn.close()
        return redirect(url_for('feedback_list'))

@app.route('/login/',methods=['POST','GET'])
def login():
    if request.method=='POST':
        username = request.form.get('username')
        sql = 'select Password from UserInfo where UserName=?'
        result = query_sql(sql,(username,),True)
        spwd = result.get('Password') if result else  None
        if spwd is None:
            flash('无此用户 错误！')
            return render_template('login.html')
        if check_password_hash(spwd,str(request.form.get('password'))) :
        # sql='select count(*) as cnt from UserInfo where UserName=? and Password=?'
        # result = query_sql(sql,(username,pwd),True)
        # if int(result.get('cnt'))>0:
            # 设置session时间
            session.permanent = True
            app.permanent_session_lifetime = timedelta(minutes=10)
            session['admin'] = username
            # 构建response,设置Cookie
            resp = make_response(redirect(url_for('feedback_list')))
            resp.set_cookie('username', username, expires=datetime.now() + timedelta(minutes=10), path='/')
            # 如何 return resp ?
            return resp

        flash('密码错误！')
    return render_template('login.html')

@app.route('/logout/')
def logout():
    if session.get('admin',None):
        session.pop('admin')
    # 防止浏览器回退不指向login
    return redirect(url_for('feedback_list'))

@app.route('/setck/')
def set_mycookie():
    resp = make_response('Cookie set ok')
    resp.set_cookie('username','mytest_cookie',expires=datetime.now()+timedelta(minutes=10),path='/')
    return resp

@app.route('/getck/')
def get_mycookie():
    ck = request.cookies.get('username',None)
    if ck:
        return ck
    return '未找到cookie'

@app.route('/rmck/')
def remove_cookie():
    resp = make_response('删除Cookie')
    resp.set_cookie('username','',expires=0)
    return resp

# @app.route('/reg2/')
# def reg2():
#     return render_template('reg.html')
# 为导入的基于类的视图添加URL规则
app.add_url_rule('/reg/',view_func=RegUser.as_view('reg_user'))
#为基于方法的视图添加URL规则
app.add_url_rule('/regadmin/',view_func=RegAdmin.as_view('reg_admin'))

if __name__ == '__main__':
    print(app.url_map)
    app.run()


